package Helpers;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Database.ConnectDB;
import Entities.Admin;
import Entities.Candidate;
import Entities.Panel;
import Entities.Voter;

public class DBMethods {

	public Voter getStudent(String username,String password)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.student s;";
		ResultSet rs = ConnectDB.executeQuery(query);
		try {
			while(rs.next())
			{
				if(rs.getString(2).equals(username) && rs.getString(3).equals(password))
				{
					return new Voter(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6));
				}
			}
			c.commit();
			c.close();
			return null;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	public Admin isAdminExists(String username,String password)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.admin a;";
		ResultSet rs = ConnectDB.executeQuery(query);
		try {
			while(rs.next())
			{
				if(rs.getString(2).equals(username) && rs.getString(3).equals(password))
				{
					return new Admin(rs.getInt(1),rs.getString(2),rs.getString(3));
				}
			}
			c.commit();
			c.close();
			return null;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Candidate> getCandidatesThatCanBeVoted(String semster,String vid)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.candidate c where c.semster = '"+semster+"' and c.cid not in (select c2.cid from candidate c2,voted v where c2.cid=v.cid and uid = '"+vid+"');";
		ResultSet rs = ConnectDB.executeQuery(query);
		ArrayList<Candidate> cs = new ArrayList<Candidate>();
		try {
			while(rs.next())
			{
				cs.add(new Candidate(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5)));
			}
			c.commit();
			c.close();
			return cs;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Candidate> getCandidates()
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.candidate c;";
		ResultSet rs = ConnectDB.executeQuery(query);
		ArrayList<Candidate> cs = new ArrayList<Candidate>();
		try {
			while(rs.next())
			{
				cs.add(new Candidate(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5)));
			}
			c.commit();
			c.close();
			return cs;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	
	public int getNoOfCandidates()
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT count(*) FROM voting.candidate c;";
		ResultSet rs = ConnectDB.executeQuery(query);
		int count = -1;
		try {
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			c.commit();
			c.close();
			return count;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return -1;
		}
	}
	
	public int getNoOfCandidatesThatCanBeVoted(String semster,String vid)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT count(*) FROM voting.candidate c where c.semster = '"+semster+"' and c.cid not in (select c2.cid from candidate c2,voted v where c2.cid=v.cid and uid = '"+vid+"');";
		ResultSet rs = ConnectDB.executeQuery(query);
		int count = -1;
		try {
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			c.commit();
			c.close();
			return count;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return -1;
		}
	}
	
	public int getNoOfVotesForCandidate(int candidateID)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT count(*) FROM voting.voted v where cid = "+candidateID+";";
		ResultSet rs = ConnectDB.executeQuery(query);
		int noOfVotes = -1;
		try {
			if(rs.next())
			{
				noOfVotes = rs.getInt(1);
			}
			c.commit();
			c.close();
			return noOfVotes;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return -1;
		}
	}
	
	public ArrayList<Panel> getPanels()
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.panel p;";
		ResultSet rs = ConnectDB.executeQuery(query);
		ArrayList<Panel> ps = new ArrayList<Panel>();
		try {
			while(rs.next())
			{
				ps.add(new Panel(rs.getInt(1), rs.getString(2)));
			}
			c.commit();
			c.close();
			return ps;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	public int insertVote(String uid,int cid)
	{
		try
		{
			Connection c = ConnectDB.createConnection();
			String query = "insert into voted (uid,cid) values ('"+uid+"',"+cid+");";
			int rs = ConnectDB.executeNonQuery(query);
			c.commit();
			c.close();
			return rs;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public int insertPanel(String category)
	{
		try
		{
			Connection c = ConnectDB.createConnection();
			String query = "insert into voting.panel (category) values ('"+category+"');";
			int rs = ConnectDB.executeNonQuery(query);
			c.commit();
			c.close();
			return rs;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public Voter getStudentByEmail(String email)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT * FROM voting.student s where s.email = '"+email+"';";
		ResultSet rs = ConnectDB.executeQuery(query);
		try {
			if(rs.next())
			{
				return new Voter(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6));
			}
			c.commit();
			c.close();
			return null;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
	}
	
	public int updateStudentByEmail(String email,String password,int activated)
	{
		try {
			Connection c = ConnectDB.createConnection();
			String query = "update voting.student s set activated = "+activated+" , password='"+password+"' where s.email = '"+email+"';";
			int rs = ConnectDB.executeNonQuery(query);
			c.commit();
			c.close();
			return rs;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return -1;
		}
	}
	
	public boolean alreadyVoted(String uid)
	{
		Connection c = ConnectDB.createConnection();
		String query = "SELECT count(*) FROM voting.voted v where v.uid = '"+uid+"';";
		ResultSet rs = ConnectDB.executeQuery(query);
		try {
			if(rs.next())
			{
				if(Integer.parseInt(rs.getString(1)) == 0)
				{
					return false;
				}
			}
			c.commit();
			c.close();
			return true;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return true;
		}
	}
}
